x 'cd U:\sas-quatch';
%inc 'itunes\itunes.sas';

*sasfile music load;

proc freq data=music noprint;
  table album / out=dim_album;
  table artist/out=dim_artist;
run;
proc sql;
  create table artist_album as
  select distinct
    artist
  , album
  , max(track_age) as album_age
  , count(*) as album_track_count
  , sum(total_time) as album_total_time format=time.
  , sum(play_count) as album_play_count
  , calculated album_play_count / calculated album_track_count as album_plays_per_track format=5.2
  , mean(play_count / track_age / total_time * 60) as plays_per_day
  , calculated album_play_count / calculated album_age as plays_per_day2
  from music
  group by artist, album
  having (album_track_count ge 5 or album_total_time ge '00:20:00't) and play_count gt 0
  ;
quit;

proc reg data=music;
  model play_count = rating;
run;
quit;

*sasfile music close;

/************************************************
TODO:
- derive track rating based on track age, play count, duration
- normalize the itunes library into star schema relational tables?
************************************************/
